var queues = require('mysql-queues');
const DEBUG = true;
var query = function(connection,keyword, callback) {
	//var sql = 'select u.id,u.userName,u.password,u.employeeId,e.name,r.id as rid,r.roleName from user u,user_role ur,role r,employee e where u.id=ur.userId and u.employeeId=e.id';
	var sql = 'select m.id,m.userName,m.password,m.employeeId,m.name,r.id as rid,r.roleName from (select u.*,ur.roleId from (select user.*,employee.name from user left join employee on user.employeeId=employee.id) u left join user_role ur on u.id=ur.userId) m left join role r on m.roleId=r.id where m.userName like ? or m.name like ? or r.roleName = ?';
	connection.query(sql,['%' + keyword + '%', '%' + keyword + '%',  keyword], function(err, rows) {
		if (err){
			console.log(err);
			console.log(err.stack);
		}
		callback(rows);
		connection.release();
	});
},
	add = function(connection, data, callback) {
		queues(connection, DEBUG);
		var sql = 'insert into user(userName,password,employeeId) values(?,?,?)';
		var sqlRole = 'insert into user_role(userId, roleId) values(?,?)';
		var trans = connection.startTransaction();
		var role = data.role;
		trans.query(sql, [data.userName, data.password, data.employeeId], function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				for (var i = 0; i < role.length; i++) {
					console.log('id:',role[i]);
					trans.query(sqlRole, [rows.insertId, role[i]], function(err, data) {
						if (err) {
							trans.rollback();
							return;
						}
					});
				}
				trans.commit();
			}
			callback(rows);
		});
		trans.execute();
		console.log('execute add');
		connection.release();
	},
	delet = function(connection, id, callback) {
		queues(connection, DEBUG);
		var sqlUserRole = 'delete from user_role where userId=?';
		var sql = 'delete from user where id=?';
		var trans = connection.startTransaction();
		trans.query(sqlUserRole, id, function(err, rows) {
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.query(sql, id, function(err, rows) {
					trans.commit();
				});

			}
			//console.log('rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute');
		connection.release();
	},
	update = function(connection, data, callback) {
		queues(connection, DEBUG);
		//修改用户表
		var sql = 'update user set userName=?,password=?,employeeId=? where id=?';
		//删掉原来的用户角色信息
		var sqlDeletOld = 'delete from user_role where userId=?';
		//增加新的用户角色信息
		var sqlUserRole = 'insert into user_role(userId,roleId) values(?,?)';
		var trans = connection.startTransaction();
		var role = data.role;
		trans.query(sql, [data.userName, data.password, data.employeeId, data.userId], function(err, rows) {
		//	console.log('updatesql:', sql);
			if (err) {
				console.log(err);
				console.log(err.stack);
				trans.rollback();
			} else {
				trans.query(sqlDeletOld, data.userId, function(err, row) {
					if (err) {
						console.log(err);
						console.log(err.stack);
						trans.rollback();
					} else {
						for (var i = 0; i < role.length; i++) {
							trans.query(sqlUserRole, [data.userId,role[i]], function(err, row) {
								if (err) {
									console.log(err);
									console.log(err.stack);
									trans.rollback();
								}
							});
						}
						trans.commit();
					}
				});
			}
			// console.log('rows', rows);
			callback(rows);
		});
		trans.execute();
		console.log('execute update');
		connection.release();
	},
	findOne = function(connection, name, callback) {
		var sql = 'select * from user where userName=?';
		connection.query(sql, name, function(err, rows) {
			if (err) {
				console('err:1111', err);
			}
			connection.release();
			callback(rows);
		});
	},
	findById = function(connection, id, callback) {
		var sql = 'select * from user where id=?';
		connection.query(sql, id, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	findEmployeeMsgByUid = function(connection, id, callback) {
		var sql = 'select e.workplace from user u ,employee e where u.employeeId=e.id and u.id=?';
		connection.query(sql, id, function(err, rows) {
			callback(rows);
			connection.release();
		});
	},
	findResource = function(connection, id, callback){
		var sql = 'select r.resUrl,b.* from resource r left join (select role_resource.*,a.userId from role_resource right join(select roleId,userId from user_role where userId=?)a on a.roleId=role_resource.roleId)b on r.id=b.resId';
		connection.query(sql, id, function(err,rows){
			var data = [];
			for(var i = 0; i < rows.length; i ++){
				if(rows[i].roleId != null && rows[i].roleId != undefined){
					data.push(rows[i]);
				}
			}
			// console.log('llllll',data);
			// console.log('oooooo',rows);
			callback(data);
			connection.release();
		});
	},
	changePwd = function(connection, data, callback){
		var sql = 'update user set password=? where id=?';
		connection.query(sql, [data.password, data.id], function(err, rows){
			if(err) {
				console.log(err);
				console.log(err.stack);
			}

			callback(rows);
			connection.release();
		});
	};

module.exports = {
	query: query,
	add: add,
	delet: delet,
	update: update,
	findOne: findOne,
	findEmployeeMsgByUid: findEmployeeMsgByUid,
	findResource: findResource,
	changePwd: changePwd
};